/**
 * wordController.js
 * 单词学习相关功能的控制器
 * 包含单词获取、艾宾浩斯复习规划等功能
 */

const db = require('../db');

// 艾宾浩斯遗忘曲线的复习间隔（单位：小时）
const REVIEW_INTERVALS = [
  24,      // 1天后复习
  48,      // 2天后复习
  96,      // 4天后复习
  168,     // 7天后复习
  360,     // 15天后复习
  720      // 30天后复习
];

// 用户等级对应的难度范围
const USER_LEVEL_DIFFICULTIES = {
  'beginner': { min: 1, max: 2, targetAccuracy: 0.8 },
  'intermediate': { min: 2, max: 3, targetAccuracy: 0.75 },
  'advanced': { min: 3, max: 4, targetAccuracy: 0.7 },
  'expert': { min: 4, max: 5, targetAccuracy: 0.65 }
};

// 自适应难度调整阈值
const DIFFICULTY_ADJUSTMENT_THRESHOLD = {
  increase: 0.85,  // 如果正确率高于85%，提高难度
  decrease: 0.6    // 如果正确率低于60%，降低难度
};

/**
 * 动态选择适合用户水平的单词难度
 * @param {number} userId - 用户ID
 * @returns {Promise<Object>} 包含推荐的难度范围和学习能力评估的对象
 */
async function dynamicWordDifficultySelection(userId) {
  try {
    // 获取用户信息和学习能力
    const [userRows] = await db.execute(
      `SELECT 
        id, 
        username, 
        current_level,
        daily_new_words_limit
      FROM user 
      WHERE id = ?`,
      [userId]
    );
    
    if (userRows.length === 0) {
      throw new Error('用户不存在');
    }
    
    const user = userRows[0];
    // 默认为beginner级别，如果用户没有设置
    const userLevel = user.current_level || 'beginner';
    
    // 获取用户的学习表现数据
    const [performanceRows] = await db.execute(
      `SELECT 
        COUNT(*) as total_answers,
        SUM(CASE WHEN last_answer_correct = TRUE THEN 1 ELSE 0 END) as correct_answers,
        AVG(mastery_level) as avg_mastery_level,
        COUNT(CASE WHEN mastered = TRUE THEN 1 END) as mastered_count
      FROM user_word_study 
      WHERE user_id = ?`,
      [userId]
    );
    
    // 获取用户最近学习的单词难度分布
    const [recentDifficultyRows] = await db.execute(
      `SELECT 
        w.difficulty,
        COUNT(*) as count,
        SUM(CASE WHEN uws.last_answer_correct = TRUE THEN 1 ELSE 0 END) as correct_count
      FROM user_word_study uws
      JOIN words w ON uws.word_id = w.id
      WHERE uws.user_id = ? AND uws.last_review_time > DATE_SUB(NOW(), INTERVAL 14 DAY)
      GROUP BY w.difficulty
      ORDER BY w.difficulty`,
      [userId]
    );
    
    // 计算总正确率和每个难度级别的正确率
    const performance = performanceRows[0];
    const totalAnswers = performance.total_answers || 0;
    const correctAnswers = performance.correct_answers || 0;
    const overallAccuracy = totalAnswers > 0 ? correctAnswers / totalAnswers : 0;
    
    // 默认难度范围
    let defaultRange = USER_LEVEL_DIFFICULTIES[userLevel] || USER_LEVEL_DIFFICULTIES.beginner;
    let minDifficulty = defaultRange.min;
    let maxDifficulty = defaultRange.max;
    let targetAccuracy = defaultRange.targetAccuracy;
    
    // 初始化难度分析
    const difficultyAnalysis = {};
    for (let i = 1; i <= 5; i++) {
      difficultyAnalysis[i] = {
        count: 0,
        correctCount: 0,
        accuracy: 0
      };
    }
    
    // 填充难度分析数据
    recentDifficultyRows.forEach(row => {
      const difficulty = parseInt(row.difficulty) || 1;
      difficultyAnalysis[difficulty] = {
        count: parseInt(row.count) || 0,
        correctCount: parseInt(row.correct_count) || 0,
        accuracy: row.count > 0 ? row.correct_count / row.count : 0
      };
    });
    
    // 基于学习表现动态调整难度范围
    if (totalAnswers >= 50) {  // 至少有足够的样本数据
      // 检查用户在各难度级别的表现
      let highestWorkingDifficulty = 0;
      
      // 从低到高找到用户能够应对的最高难度
      for (let i = 1; i <= 5; i++) {
        const analysis = difficultyAnalysis[i];
        if (analysis.count >= 10 && analysis.accuracy >= targetAccuracy) {
          highestWorkingDifficulty = i;
        } else if (analysis.count >= 10) {
          // 如果尝试次数足够但正确率不达标，停止检查更高难度
          break;
        }
      }
      
      // 基于用户表现调整难度范围
      if (highestWorkingDifficulty > 0) {
        // 用户在此难度下表现良好，可以尝试更高难度
        if (overallAccuracy > DIFFICULTY_ADJUSTMENT_THRESHOLD.increase) {
          minDifficulty = Math.min(highestWorkingDifficulty, 4);  // 最高到4级
          maxDifficulty = Math.min(highestWorkingDifficulty + 1, 5);  // 最高到5级
        } 
        // 用户在此难度下表现不佳，降低难度
        else if (overallAccuracy < DIFFICULTY_ADJUSTMENT_THRESHOLD.decrease) {
          minDifficulty = Math.max(highestWorkingDifficulty - 1, 1);  // 最低到1级
          maxDifficulty = Math.max(highestWorkingDifficulty, 2);  // 至少保持到2级
        }
        // 表现适中，维持难度范围
        else {
          minDifficulty = Math.max(highestWorkingDifficulty - 1, 1);
          maxDifficulty = highestWorkingDifficulty;
        }
      }
    }
    
    return {
      userLevel,
      recommendedDifficulty: {
        min: minDifficulty,
        max: maxDifficulty
      },
      learningPerformance: {
        totalAnswers,
        correctAnswers,
        overallAccuracy,
        avgMasteryLevel: performance.avg_mastery_level || 0,
        masteredCount: performance.mastered_count || 0
      },
      difficultyAnalysis
    };
  } catch (error) {
    console.error('动态难度选择失败:', error);
    // 如果发生错误，返回默认的难度范围
    return {
      userLevel: 'beginner',
      recommendedDifficulty: USER_LEVEL_DIFFICULTIES.beginner,
      learningPerformance: {
        totalAnswers: 0,
        correctAnswers: 0,
        overallAccuracy: 0,
        avgMasteryLevel: 0,
        masteredCount: 0
      },
      difficultyAnalysis: {}
    };
  }
}

/**
 * 获取单个单词详情
 * @param {number} wordId - 单词ID
 * @returns {Promise<Object|null>} 单词信息或null
 */
async function getWordById(wordId) {
  try {
    const [rows] = await db.execute(
      `SELECT 
        id, 
        word, 
        mean, 
        phonetic_symbol, 
        initial, 
        difficulty, 
        frequency, 
        topic, 
        part_of_speech, 
        root
      FROM words 
      WHERE id = ?`,
      [wordId]
    );

    if (rows.length === 0) {
      return null;
    }

    return rows[0];
  } catch (error) {
    console.error('获取单词详情失败:', error);
    throw new Error('获取单词详情失败: ' + error.message);
  }
}

/**
 * 获取单词列表
 * @param {Object} options - 查询选项
 * @param {number} [options.page=1] - 页码
 * @param {number} [options.pageSize=20] - 每页数量
 * @param {string} [options.initial] - 首字母筛选
 * @param {string} [options.topic] - 主题筛选
 * @param {number} [options.difficulty] - 难度筛选
 * @returns {Promise<Object>} 包含单词列表和总数的对象
 */
async function getWordsList(options) {
  // 确保参数为数字
  const page = parseInt(options.page) || 1;
  const pageSize = parseInt(options.pageSize) || 20;
  const offset = (page - 1) * pageSize;
  
  try {
    // 构建WHERE条件
    let whereConditions = [];
    let params = [];
    
    if (options.initial) {
      whereConditions.push('initial = ?');
      params.push(options.initial);
    }
    
    if (options.topic) {
      whereConditions.push('topic = ?');
      params.push(options.topic);
    }
    
    if (options.difficulty) {
      whereConditions.push('difficulty = ?');
      params.push(options.difficulty);
    }
    
    const whereClause = whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : '';
    
    // 获取总数
    let countSql = `SELECT COUNT(*) as total FROM words`;
    if (whereClause) {
      countSql += ' ' + whereClause;
    }
    
    console.log('Count SQL:', countSql);
    console.log('Count params:', params);
    
    const [countRows] = await db.execute(countSql, params);
    const total = countRows[0].total;
    
    // 构建获取列表的SQL，但先不包含LIMIT和OFFSET
    let listSql = `
      SELECT 
        id, 
        word, 
        mean, 
        phonetic_symbol, 
        initial, 
        difficulty, 
        frequency, 
        topic, 
        part_of_speech
      FROM words
    `;
    
    if (whereClause) {
      listSql += ' ' + whereClause;
    }
    
    // 添加排序
    listSql += ' ORDER BY id ASC';
    
    // 直接使用字符串拼接来添加LIMIT和OFFSET，避免参数传递问题
    // 此方法不是最佳实践（有SQL注入风险），但在确保page和pageSize是整数的情况下是安全的
    const finalSql = `${listSql} LIMIT ${pageSize} OFFSET ${offset}`;
    
    console.log('Final SQL:', finalSql);
    
    // 直接执行SQL查询，不需要传递LIMIT和OFFSET作为参数
    const [rows] = await db.query(finalSql, params);
    
    return {
      total,
      pageSize,
      currentPage: page,
      totalPages: Math.ceil(total / pageSize),
      list: rows
    };
  } catch (error) {
    console.error('获取单词列表失败:', error);
    throw new Error('获取单词列表失败: ' + error.message);
  }
}

/**
 * 获取用户每日学习单词（包含新词和复习词）
 * @param {number} userId - 用户ID
 * @param {Object} options - 选项
 * @param {number} [options.newWordsLimit] - 新词数量限制
 * @param {number} [options.reviewWordsLimit] - 复习词数量限制
 * @returns {Promise<Object>} 包含新词和复习词的对象
 */
async function getDailyStudyWords(userId, options = {}) {
  try {
    // 确保用户ID是整数
    userId = parseInt(userId);
    
    // 获取用户设置
    const [userRows] = await db.execute(
      `SELECT 
        daily_new_words_limit, 
        daily_review_limit 
      FROM user 
      WHERE id = ?`,
      [userId]
    );
    
    if (userRows.length === 0) {
      throw new Error('用户不存在');
    }
    
    const user = userRows[0];
    
    // 使用提供的限制或默认值，并确保是整数
    const newWordsLimit = parseInt(options.newWordsLimit) || parseInt(user.daily_new_words_limit) || 20;
    const reviewWordsLimit = parseInt(options.reviewWordsLimit) || parseInt(user.daily_review_limit) || 50;
    
    // 获取今天的日期（不含时间）
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    
    // 检查今天已学习的单词数量
    const [studyRecords] = await db.execute(
      `SELECT 
        new_words_count, 
        review_words_count 
      FROM daily_study_records 
      WHERE user_id = ? AND DATE(study_date) = DATE(?)`,
      [userId, today]
    );
    
    let newWordsStudied = 0;
    let reviewWordsStudied = 0;
    
    if (studyRecords.length > 0) {
      newWordsStudied = parseInt(studyRecords[0].new_words_count) || 0;
      reviewWordsStudied = parseInt(studyRecords[0].review_words_count) || 0;
    }
    
    // 计算还可以学习的数量
    const newWordsToLearn = Math.max(0, newWordsLimit - newWordsStudied);
    const reviewWordsToLearn = Math.max(0, reviewWordsLimit - reviewWordsStudied);
    
    console.log('User ID:', userId);
    console.log('Review words to learn:', reviewWordsToLearn);
    
    // 获取需要复习的单词（根据复习时间）
    let reviewWords = [];
    if (reviewWordsToLearn > 0) {
      // 使用字符串拼接的方式处理LIMIT
      const reviewSql = `
        SELECT 
          w.id, 
          w.word, 
          w.mean, 
          w.phonetic_symbol, 
          w.initial, 
          w.difficulty, 
          w.frequency, 
          w.topic, 
          w.part_of_speech,
          uws.mastery_level,
          uws.review_stage,
          uws.review_count
        FROM words w
        JOIN user_word_study uws ON w.id = uws.word_id
        WHERE uws.user_id = ? 
          AND uws.mastered = FALSE
          AND uws.next_review_time <= ?
        ORDER BY uws.next_review_time ASC
        LIMIT ${reviewWordsToLearn}
      `;
      
      console.log('Review SQL:', reviewSql);
      
      const [rows] = await db.query(reviewSql, [userId, new Date()]);
      reviewWords = rows;
    }
    
    // 获取用户已学习的单词ID（包括已掌握和未掌握的）
    const [learnedWordIds] = await db.execute(
      `SELECT word_id FROM user_word_study WHERE user_id = ?`,
      [userId]
    );
    
    const excludeIds = learnedWordIds.map(row => row.word_id);
    
    // 获取新单词（未学习的）以及难度动态调整
    let newWords = [];
    if (newWordsToLearn > 0) {
      // 获取用户的推荐难度范围
      const difficultyRecommendation = await dynamicWordDifficultySelection(userId);
      const minDifficulty = difficultyRecommendation.recommendedDifficulty.min;
      const maxDifficulty = difficultyRecommendation.recommendedDifficulty.max;
      
      console.log('用户推荐难度范围:', minDifficulty, '到', maxDifficulty);
      
      // 如果没有已学习的单词，直接获取推荐难度范围内的新单词
      if (excludeIds.length === 0) {
        const newWordsSql = `
          SELECT 
            id, 
            word, 
            mean, 
            phonetic_symbol, 
            initial, 
            difficulty, 
            frequency, 
            topic, 
            part_of_speech
          FROM words
          WHERE difficulty BETWEEN ? AND ?
          ORDER BY frequency DESC, difficulty ASC, id ASC
          LIMIT ${newWordsToLearn}
        `;
        
        console.log('New words SQL (no excludes):', newWordsSql);
        console.log('Difficulty range:', minDifficulty, maxDifficulty);
        
        const [rows] = await db.query(newWordsSql, [minDifficulty, maxDifficulty]);
        newWords = rows;
      } else {
        // 如果有已学习的单词，排除它们并按推荐难度范围筛选
        // 使用NOT IN时，需要确保IN子句中有足够的占位符
        
        // 构建带有足够占位符的查询
        const placeholders = excludeIds.map(() => '?').join(',');
        const newWordsSql = `
          SELECT 
            id, 
            word, 
            mean, 
            phonetic_symbol, 
            initial, 
            difficulty, 
            frequency, 
            topic, 
            part_of_speech
          FROM words
          WHERE id NOT IN (${placeholders})
            AND difficulty BETWEEN ? AND ?
          ORDER BY frequency DESC, difficulty ASC, id ASC
          LIMIT ${newWordsToLearn}
        `;
        
        console.log('New words SQL (with excludes):', newWordsSql);
        console.log('Difficulty range:', minDifficulty, maxDifficulty);
        
        // 在params末尾添加难度范围参数
        const params = [...excludeIds, minDifficulty, maxDifficulty];
        const [rows] = await db.query(newWordsSql, params);
        newWords = rows;
      }
    }
    
    return {
      newWords,
      reviewWords,
      dailyLimits: {
        newWordsLimit,
        reviewWordsLimit,
        newWordsStudied,
        reviewWordsStudied,
        newWordsToLearn,
        reviewWordsToLearn
      },
      // 添加难度推荐信息
      difficultyRecommendation: newWordsToLearn > 0 ? 
        await dynamicWordDifficultySelection(userId) : null
    };
  } catch (error) {
    console.error('获取每日学习单词失败:', error);
    throw new Error('获取每日学习单词失败: ' + error.message);
  }
}

/**
 * 记录单词学习情况
 * @param {number} userId - 用户ID
 * @param {number} wordId - 单词ID
 * @param {boolean} isCorrect - 是否回答正确
 * @param {boolean} isNewWord - 是否新词
 * @returns {Promise<Object>} 包含下次复习时间等信息的对象
 */
async function recordWordStudy(userId, wordId, isCorrect, isNewWord) {
  try {
    // 获取当前时间
    const now = new Date();
    
    // 获取单词信息
    const [wordRows] = await db.execute(
      `SELECT difficulty FROM words WHERE id = ?`,
      [wordId]
    );
    
    if (wordRows.length === 0) {
      throw new Error('单词不存在');
    }
    
    const wordDifficulty = parseInt(wordRows[0].difficulty) || 3; // 默认中等难度
    
    // 检查单词学习记录是否存在
    const [recordRows] = await db.execute(
      `SELECT * FROM user_word_study WHERE user_id = ? AND word_id = ?`,
      [userId, wordId]
    );
    
    let mastery_level = 0;
    let review_stage = 0;
    let review_count = 0;
    let total_correct = 0;
    let total_incorrect = 0;
    let consecutive_correct = 0;
    let previous_interval = 0;
    
    if (recordRows.length > 0) {
      // 已存在记录，更新
      const record = recordRows[0];
      mastery_level = record.mastery_level || 0;
      review_stage = record.review_stage || 0;
      review_count = record.review_count || 0;
      total_correct = record.total_correct || 0;
      total_incorrect = record.total_incorrect || 0;
      consecutive_correct = record.consecutive_correct || 0;
      previous_interval = record.previous_interval || 0;
      
      if (isCorrect) {
        // 答对，提高掌握度和连续正确次数
        mastery_level = Math.min(mastery_level + 1, 10); // 提高掌握度上限到10
        consecutive_correct++;
        
        // 根据连续正确次数和单词难度调整复习阶段
        if (consecutive_correct >= 2) {
          // 连续答对2次以上，更快地提升复习阶段
          const stageIncrease = Math.min(Math.floor(consecutive_correct / 2), 2);
          review_stage = Math.min(review_stage + stageIncrease, REVIEW_INTERVALS.length - 1);
        } else {
          // 正常提升复习阶段
          review_stage = Math.min(review_stage + 1, REVIEW_INTERVALS.length - 1);
        }
        
        total_correct++;
      } else {
        // 答错，降低掌握度，重置连续正确次数，降低复习阶段
        const masteryDecrease = Math.max(1, Math.floor(wordDifficulty / 2)); // 难度越高，错误扣分越多
        mastery_level = Math.max(mastery_level - masteryDecrease, 0);
        consecutive_correct = 0;
        
        // 根据当前掌握度决定复习阶段重置程度
        if (mastery_level <= 2) {
          // 掌握度很低，完全重置复习阶段
          review_stage = 0;
        } else if (mastery_level <= 5) {
          // 掌握度中等，降低复习阶段但不完全重置
          review_stage = Math.max(review_stage - 2, 0);
        } else {
          // 掌握度高，轻微降低复习阶段
          review_stage = Math.max(review_stage - 1, 0);
        }
        
        total_incorrect++;
      }
      
      review_count++;
    } else {
      // 新建记录
      if (isCorrect) {
        // 第一次就答对，给予较高的初始掌握度
        mastery_level = 2;
        review_stage = 1;
        consecutive_correct = 1;
        total_correct = 1;
      } else {
        // 第一次就答错，给予较低的初始掌握度
        mastery_level = 0;
        review_stage = 0;
        consecutive_correct = 0;
        total_incorrect = 1;
      }
      
      review_count = 1;
    }
    
    // 获取基础复习间隔（小时）
    let baseInterval = REVIEW_INTERVALS[review_stage] || REVIEW_INTERVALS[0];
    
    // 根据单词难度和掌握度动态调整复习间隔
    let intervalMultiplier = 1.0;
    
    // 单词难度因子：难度越高，间隔越短
    const difficultyFactor = Math.max(0.7, 1.0 - (wordDifficulty - 1) * 0.05);
    
    // 掌握度因子：掌握度越高，间隔越长
    const masteryFactor = Math.min(1.5, 0.8 + (mastery_level * 0.07));
    
    // 连续正确因子：连续正确次数越多，间隔越长
    const consecutiveFactor = Math.min(1.3, 1.0 + (consecutive_correct * 0.05));
    
    // 结合所有因子
    intervalMultiplier = difficultyFactor * masteryFactor * consecutiveFactor;
    
    // 应用间隔倍数
    const adjustedInterval = Math.round(baseInterval * intervalMultiplier);
    
    // 计算下次复习时间
    const nextReviewTime = new Date(now.getTime() + adjustedInterval * 60 * 60 * 1000);
    
    // 判断是否已掌握（掌握度达到8及以上）
    const mastered = mastery_level >= 8;
    
    // 更新或插入单词学习记录
    if (recordRows.length > 0) {
      await db.execute(
        `UPDATE user_word_study SET
          mastery_level = ?,
          review_stage = ?,
          review_count = ?,
          total_correct = ?,
          total_incorrect = ?,
          consecutive_correct = ?,
          previous_interval = ?,
          mastered = ?,
          last_review_time = ?,
          next_review_time = ?,
          last_answer_correct = ?
        WHERE user_id = ? AND word_id = ?`,
        [
          mastery_level,
          review_stage,
          review_count,
          total_correct,
          total_incorrect,
          consecutive_correct,
          adjustedInterval,
          mastered,
          now,
          nextReviewTime,
          isCorrect,
          userId,
          wordId
        ]
      );
    } else {
      await db.execute(
        `INSERT INTO user_word_study (
          user_id,
          word_id,
          mastery_level,
          review_stage,
          review_count,
          total_correct,
          total_incorrect,
          consecutive_correct,
          previous_interval,
          mastered,
          last_review_time,
          next_review_time,
          last_answer_correct,
          created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          userId,
          wordId,
          mastery_level,
          review_stage,
          review_count,
          total_correct,
          total_incorrect,
          consecutive_correct,
          adjustedInterval,
          mastered,
          now,
          nextReviewTime,
          isCorrect,
          now
        ]
      );
    }
    
    // 更新每日学习记录
    const todayDate = new Date(now);
    todayDate.setHours(0, 0, 0, 0);
    
    const [dailyRecords] = await db.execute(
      `SELECT id FROM daily_study_records WHERE user_id = ? AND DATE(study_date) = DATE(?)`,
      [userId, todayDate]
    );
    
    if (dailyRecords.length > 0) {
      // 更新今日学习记录
      await db.execute(
        `UPDATE daily_study_records SET
          ${isNewWord ? 'new_words_count = new_words_count + 1' : 'review_words_count = review_words_count + 1'},
          ${isCorrect ? 'correct_count = correct_count + 1' : 'incorrect_count = incorrect_count + 1'}
        WHERE id = ?`,
        [dailyRecords[0].id]
      );
    } else {
      // 创建今日学习记录
      await db.execute(
        `INSERT INTO daily_study_records (
          user_id,
          study_date,
          new_words_count,
          review_words_count,
          correct_count,
          incorrect_count
        ) VALUES (?, ?, ?, ?, ?, ?)`,
        [
          userId,
          todayDate,
          isNewWord ? 1 : 0,
          isNewWord ? 0 : 1,
          isCorrect ? 1 : 0,
          isCorrect ? 0 : 1
        ]
      );
    }
    
    // 更新单词的全局错误率
    if (!isCorrect) {
      await updateWordErrorRate(wordId);
    }
    
    // 返回学习结果及详细信息
    return {
      mastery_level,
      review_stage,
      next_review_time: nextReviewTime,
      interval_hours: adjustedInterval,
      mastered,
      // 添加更多学习进度信息
      learning_details: {
        total_correct,
        total_incorrect,
        consecutive_correct,
        accuracy: review_count > 0 ? (total_correct / review_count) : 0,
        difficulty: wordDifficulty,
        interval_factors: {
          base: baseInterval,
          difficulty_factor: difficultyFactor,
          mastery_factor: masteryFactor,
          consecutive_factor: consecutiveFactor,
          final_multiplier: intervalMultiplier
        }
      }
    };
  } catch (error) {
    console.error('记录单词学习失败:', error);
    throw new Error('记录单词学习失败: ' + error.message);
  }
}

/**
 * 更新单词的全局错误率
 * @param {number} wordId - 单词ID
 * @returns {Promise<void>}
 */
async function updateWordErrorRate(wordId) {
  try {
    // 计算该单词的全局错误率
    const [stats] = await db.execute(
      `SELECT 
        SUM(total_incorrect) as total_incorrect,
        SUM(total_correct + total_incorrect) as total_attempts
      FROM user_word_study 
      WHERE word_id = ?`,
      [wordId]
    );
    
    if (stats.length > 0 && stats[0].total_attempts > 0) {
      const errorRate = stats[0].total_incorrect / stats[0].total_attempts;
      
      // 更新单词的错误率
      await db.execute(
        `UPDATE words SET error_rate = ? WHERE id = ?`,
        [errorRate, wordId]
      );
    }
  } catch (error) {
    console.error('更新单词错误率失败:', error);
  }
}

/**
 * 获取用户的学习统计数据
 * @param {number} userId - 用户ID
 * @returns {Promise<Object>} 学习统计数据
 */
async function getUserStudyStats(userId) {
  try {
    // 获取单词学习总体情况
    const [wordStats] = await db.execute(
      `SELECT 
        COUNT(*) as total_studied_words,
        SUM(CASE WHEN mastered = TRUE THEN 1 ELSE 0 END) as mastered_words,
        AVG(mastery_level) as avg_mastery_level,
        AVG(review_count) as avg_review_count
      FROM user_word_study 
      WHERE user_id = ?`,
      [userId]
    );
    
    // 获取最近7天的学习记录
    const [recentStudy] = await db.execute(
      `SELECT 
        DATE(study_date) as date,
        new_words_count,
        review_words_count,
        correct_count,
        incorrect_count,
        study_time
      FROM daily_study_records 
      WHERE user_id = ? 
      ORDER BY study_date DESC 
      LIMIT 7`,
      [userId]
    );
    
    // 获取用户总学习时间和连续天数
    const [userStats] = await db.execute(
      `SELECT 
        total_study_time,
        streak_days
      FROM user 
      WHERE id = ?`,
      [userId]
    );
    
    // 获取用户掌握水平（初级、中级、高级词汇各占比）
    const [levelStats] = await db.execute(
      `SELECT 
        w.difficulty,
        COUNT(*) as count
      FROM user_word_study uws
      JOIN words w ON uws.word_id = w.id
      WHERE uws.user_id = ? AND uws.mastered = TRUE
      GROUP BY w.difficulty`,
      [userId]
    );
    
    // 计算不同难度单词的掌握情况
    const difficultyStats = {};
    let totalMastered = 0;
    
    levelStats.forEach(stat => {
      difficultyStats[stat.difficulty] = stat.count;
      totalMastered += stat.count;
    });
    
    // 计算各难度的占比
    const levelDistribution = {};
    for (let i = 1; i <= 5; i++) {
      levelDistribution[i] = totalMastered > 0 ? 
        (difficultyStats[i] || 0) / totalMastered : 0;
    }
    
    return {
      totalStudiedWords: wordStats[0].total_studied_words || 0,
      masteredWords: wordStats[0].mastered_words || 0,
      avgMasteryLevel: wordStats[0].avg_mastery_level || 0,
      avgReviewCount: wordStats[0].avg_review_count || 0,
      totalStudyTime: userStats[0]?.total_study_time || 0,
      streakDays: userStats[0]?.streak_days || 0,
      recentStudy: recentStudy,
      levelDistribution
    };
  } catch (error) {
    console.error('获取学习统计失败:', error);
    throw new Error('获取学习统计失败: ' + error.message);
  }
}

/**
 * 分析用户学习表现并提供个性化建议
 * @param {number} userId - 用户ID
 * @returns {Promise<Object>} 学习表现分析和建议
 */
async function analyzeUserLearningPerformance(userId) {
  try {
    // 获取用户基本信息
    const [userRows] = await db.execute(
      `SELECT 
        id, 
        username, 
        current_level,
        daily_new_words_limit, 
        daily_review_limit,
        streak_days,
        total_study_time
      FROM user 
      WHERE id = ?`,
      [userId]
    );
    
    if (userRows.length === 0) {
      throw new Error('用户不存在');
    }
    
    const user = userRows[0];
    
    // 获取用户学习时段分布 (假设daily_study_records有study_time_of_day字段记录学习时间在一天中的时段)
    const [studyTimeRows] = await db.execute(
      `SELECT 
        DATE_FORMAT(study_date, '%H') as hour_of_day,
        COUNT(*) as count
      FROM daily_study_records
      WHERE user_id = ? AND study_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
      GROUP BY hour_of_day
      ORDER BY count DESC
      LIMIT 3`,
      [userId]
    );
    
    // 获取用户答题正确率随时间的变化
    const [accuracyTrendRows] = await db.execute(
      `SELECT 
        DATE(study_date) as study_day,
        CASE WHEN (correct_count + incorrect_count) > 0 
             THEN correct_count / (correct_count + incorrect_count) 
             ELSE 0 
        END as daily_accuracy
      FROM daily_study_records
      WHERE user_id = ? AND study_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
      ORDER BY study_date`,
      [userId]
    );
    
    // 获取用户在不同难度单词上的表现
    const [difficultyPerformanceRows] = await db.execute(
      `SELECT 
        w.difficulty,
        COUNT(*) as total_count,
        SUM(CASE WHEN uws.last_answer_correct = TRUE THEN 1 ELSE 0 END) as correct_count,
        AVG(uws.mastery_level) as avg_mastery
      FROM user_word_study uws
      JOIN words w ON uws.word_id = w.id
      WHERE uws.user_id = ?
      GROUP BY w.difficulty
      ORDER BY w.difficulty`,
      [userId]
    );
    
    // 获取用户掌握单词的主题分布
    const [topicDistributionRows] = await db.execute(
      `SELECT 
        w.topic,
        COUNT(*) as count,
        SUM(CASE WHEN uws.mastered = TRUE THEN 1 ELSE 0 END) as mastered_count
      FROM user_word_study uws
      JOIN words w ON uws.word_id = w.id
      WHERE uws.user_id = ?
      GROUP BY w.topic
      ORDER BY count DESC`,
      [userId]
    );
    
    // 获取用户遗忘最多的单词（复习次数多但未掌握的）
    const [problemWordsRows] = await db.execute(
      `SELECT 
        w.id,
        w.word,
        w.mean,
        w.difficulty,
        uws.review_count,
        uws.total_correct,
        uws.total_incorrect,
        uws.mastery_level
      FROM user_word_study uws
      JOIN words w ON uws.word_id = w.id
      WHERE uws.user_id = ? AND uws.mastered = FALSE
      ORDER BY uws.review_count DESC, uws.total_incorrect DESC
      LIMIT 10`,
      [userId]
    );
    
    // 计算用户整体学习表现
    const [overallPerformanceRows] = await db.execute(
      `SELECT 
        COUNT(*) as total_words,
        SUM(CASE WHEN mastered = TRUE THEN 1 ELSE 0 END) as mastered_words,
        AVG(mastery_level) as avg_mastery,
        AVG(CASE WHEN total_correct + total_incorrect > 0 
            THEN total_correct / (total_correct + total_incorrect) 
            ELSE 0 
        END) as overall_accuracy,
        AVG(review_count) as avg_reviews_per_word
      FROM user_word_study
      WHERE user_id = ?`,
      [userId]
    );
    
    // 准备分析结果
    const performance = overallPerformanceRows[0];
    const totalWords = performance.total_words || 0;
    const masteredWords = performance.mastered_words || 0;
    const masteryRate = totalWords > 0 ? (masteredWords / totalWords) : 0;
    const overallAccuracy = performance.overall_accuracy || 0;
    
    // 转换难度表现数据为更易用的格式
    const difficultyPerformance = {};
    difficultyPerformanceRows.forEach(row => {
      const difficulty = parseInt(row.difficulty) || 1;
      difficultyPerformance[difficulty] = {
        total: parseInt(row.total_count) || 0,
        correct: parseInt(row.correct_count) || 0,
        accuracy: row.total_count > 0 ? row.correct_count / row.total_count : 0,
        avgMastery: parseFloat(row.avg_mastery) || 0
      };
    });
    
    // 生成学习表现总结和建议
    const summary = {
      masteryProgress: {
        totalWords,
        masteredWords,
        masteryRate,
        remainingToMaster: totalWords - masteredWords
      },
      accuracyAnalysis: {
        overallAccuracy,
        byDifficulty: difficultyPerformance
      },
      streakStats: {
        currentStreak: user.streak_days || 0,
        totalStudyDays: accuracyTrendRows.length,
        studyConsistency: accuracyTrendRows.length > 0 ? user.streak_days / accuracyTrendRows.length : 0
      },
      topicStrengths: topicDistributionRows.map(row => ({
        topic: row.topic,
        count: parseInt(row.count) || 0,
        masteredCount: parseInt(row.mastered_count) || 0,
        masteryRate: row.count > 0 ? row.mastered_count / row.count : 0
      })),
      problemWords: problemWordsRows.map(row => ({
        id: row.id,
        word: row.word,
        meaning: row.mean,
        difficulty: parseInt(row.difficulty) || 1,
        reviewCount: parseInt(row.review_count) || 0,
        correctCount: parseInt(row.total_correct) || 0,
        incorrectCount: parseInt(row.total_incorrect) || 0,
        masteryLevel: parseInt(row.mastery_level) || 0
      })),
      preferredStudyTimes: studyTimeRows.map(row => ({
        hourOfDay: parseInt(row.hour_of_day) || 0,
        count: parseInt(row.count) || 0
      }))
    };
    
    // 生成个性化建议
    const recommendations = generatePersonalizedRecommendations(user, summary);
    
    return {
      user: {
        id: user.id,
        username: user.username,
        level: user.current_level,
        dailyNewWordsLimit: user.daily_new_words_limit,
        dailyReviewLimit: user.daily_review_limit
      },
      summary,
      recommendations,
      accuracyTrend: accuracyTrendRows.map(row => ({
        date: row.study_day,
        accuracy: parseFloat(row.daily_accuracy) || 0
      }))
    };
  } catch (error) {
    console.error('分析用户学习表现失败:', error);
    throw new Error('分析用户学习表现失败: ' + error.message);
  }
}

/**
 * 根据学习表现生成个性化建议
 * @param {Object} user - 用户信息
 * @param {Object} summary - 学习表现摘要
 * @returns {Object} 个性化建议
 */
function generatePersonalizedRecommendations(user, summary) {
  const recommendations = {
    adjustments: [],
    focus: [],
    techniques: []
  };
  
  // 分析学习量建议
  const { masteryProgress, accuracyAnalysis, streakStats } = summary;
  
  // 基于总体学习正确率推荐难度调整
  if (accuracyAnalysis.overallAccuracy > 0.9) {
    recommendations.adjustments.push({
      type: 'difficulty',
      action: 'increase',
      reason: '您的答题正确率很高，可以尝试更高难度的单词来提升学习效果'
    });
  } else if (accuracyAnalysis.overallAccuracy < 0.6) {
    recommendations.adjustments.push({
      type: 'difficulty',
      action: 'decrease',
      reason: '您的答题正确率偏低，可以尝试降低难度，先掌握基础词汇'
    });
  }
  
  // 推荐每日学习量调整
  if (streakStats.currentStreak > 7 && streakStats.studyConsistency > 0.8) {
    // 学习习惯稳定，可以增加单词量
    recommendations.adjustments.push({
      type: 'daily_words',
      action: 'increase',
      suggestion: Math.min(user.daily_new_words_limit + 5, 50),
      reason: '您有稳定的学习习惯，可以适当增加每日新单词量'
    });
  } else if (streakStats.studyConsistency < 0.5) {
    // 学习习惯不稳定，建议减少单词量
    recommendations.adjustments.push({
      type: 'daily_words',
      action: 'decrease',
      suggestion: Math.max(user.daily_new_words_limit - 5, 5),
      reason: '建议减少每日新单词量，培养稳定的学习习惯'
    });
  }
  
  // 推荐重点关注的主题
  // 找出掌握率最低的主题
  const weakTopics = summary.topicStrengths
    .filter(topic => topic.count >= 10) // 至少学习过10个该主题的单词
    .sort((a, b) => a.masteryRate - b.masteryRate)
    .slice(0, 2); // 取掌握率最低的两个主题
  
  if (weakTopics.length > 0) {
    weakTopics.forEach(topic => {
      recommendations.focus.push({
        type: 'topic',
        topic: topic.topic,
        reason: `该主题的词汇掌握率较低 (${Math.round(topic.masteryRate * 100)}%)，需要重点复习`
      });
    });
  }
  
  // 推荐复习有问题的单词
  if (summary.problemWords.length > 0) {
    recommendations.focus.push({
      type: 'problem_words',
      count: summary.problemWords.length,
      reason: '有多个高频错误单词需要重点攻克'
    });
  }
  
  // 学习技巧建议
  if (summary.preferredStudyTimes.length > 0) {
    const bestTime = summary.preferredStudyTimes[0];
    recommendations.techniques.push({
      type: 'study_time',
      time: `${bestTime.hourOfDay}:00`,
      reason: '这是您学习效果最好的时间段，可以安排更多学习任务'
    });
  }
  
  // 根据掌握情况推荐学习技巧
  if (masteryProgress.masteryRate < 0.4) {
    recommendations.techniques.push({
      type: 'focus_technique',
      technique: '间隔复习法',
      reason: '多数单词尚未掌握，建议采用间隔复习法增强记忆效果'
    });
  } else if (accuracyAnalysis.overallAccuracy < 0.7) {
    recommendations.techniques.push({
      type: 'focus_technique',
      technique: '词根词缀记忆法',
      reason: '您的整体正确率偏低，可以尝试通过词根词缀加深对单词的理解'
    });
  }
  
  return recommendations;
}

/**
 * 搜索单词
 * @param {string} keyword - 搜索关键词
 * @returns {Promise<Object>} 包含搜索结果的对象
 */
async function searchWord(keyword) {
  try {
    if (!keyword || keyword.trim() === '') {
      return { words: [] };
    }

    // 清理和规范化关键词
    keyword = keyword.trim();
    const searchPattern = `%${keyword}%`;
    
    console.log('搜索单词，关键词:', keyword);
    
    // 搜索逻辑：
    // 1. 精确匹配单词
    // 2. 模糊匹配单词
    // 3. 模糊匹配中文释义
    
    // 精确匹配单词
    const [exactMatchRows] = await db.execute(
      `SELECT 
        id, 
        word, 
        mean, 
        phonetic_symbol, 
        initial, 
        difficulty, 
        frequency, 
        topic, 
        part_of_speech
      FROM words
      WHERE word = ?
      LIMIT 1`,
      [keyword]
    );
    
    if (exactMatchRows.length > 0) {
      // 发现精确匹配，直接返回单词
      return { word: exactMatchRows[0] };
    }
    
    // 模糊搜索单词和中文释义
    const [searchRows] = await db.execute(
      `SELECT 
        id, 
        word, 
        mean, 
        phonetic_symbol, 
        initial, 
        difficulty, 
        frequency, 
        topic, 
        part_of_speech
      FROM words
      WHERE 
        word LIKE ? OR 
        mean LIKE ?
      ORDER BY 
        CASE
          WHEN word LIKE ? THEN 0  -- 开头匹配的单词优先级最高
          WHEN word LIKE ? THEN 1  -- 包含关键词的单词次之
          ELSE 2                   -- 中文释义匹配的最后
        END,
        frequency DESC, 
        difficulty ASC
      LIMIT 20`,
      [
        `${keyword}%`,   // 开头匹配
        searchPattern,   // 模糊匹配中文释义
        `${keyword}%`,   // 用于排序的开头匹配
        searchPattern    // 用于排序的模糊匹配
      ]
    );
    
    if (searchRows.length === 0) {
      return { words: [] };
    }
    
    // 如果只有一个结果，以单词详情返回
    if (searchRows.length === 1) {
      return { word: searchRows[0] };
    }
    
    // 多个结果，以列表形式返回
    return { words: searchRows };
  } catch (error) {
    console.error('搜索单词失败:', error);
    throw new Error('搜索单词失败: ' + error.message);
  }
}

module.exports = {
  getWordById,
  getWordsList,
  getDailyStudyWords,
  recordWordStudy,
  getUserStudyStats,
  analyzeUserLearningPerformance,
  searchWord
}; 